import pymysql

from front.DBhelp import DBUtui
from front.models import UIData, User, Article, photo,  UIfl


class Myfront:
    @staticmethod
    def myGllery():
        try:
            db=DBUtui.getconnect()
            cursor=db.cursor()
            sql="select id,title,simpleInfo,MainPicUrl from articles where ShowType='相册' and ispub='是';"

            cursor.execute(sql)
            data=cursor.fetchall()
            list=[]
            for temp in data:
                list.append(UIData(temp[0],temp[1],temp[3],temp[2]))
            return list
        except Exception as e:
            print('出错',e)
        finally:
            DBUtui.close(cursor,db)

    @staticmethod
    def getAlist():
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor()
            sql = "SELECT id,title,SimpleInfo,MainPicUrl FROM articles WHERE ShowType='文章' and ispub='是'"
            sql += 'order by pubTime desc'
            cursor.execute(sql)
            data = cursor.fetchall()
            list = []
            for temp in data:
                list.append(UIData(temp[0], temp[1], temp[3], temp[2]))
            return list
        except Exception as e:
            print('出错', e)
        finally:
            DBUtui.close(cursor, db)

    @staticmethod
    def getinfopic(id):
        # 相册详情
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
            sql = "SELECT a.id aid,title,a.Cont acont,PubTime,p.id pid,picurl,p.Cont pcont from articles a,photos p where a.id=p.BelongToArticles and a.id={0};".format(id)
            cursor.execute(sql)
            data = cursor.fetchall()
            aobj = Article(data[0]['aid'], data[0]['title'], '', data[0]['acont'], )
            aobj.pubtime = data[0]['PubTime']
            list = []
            for temp in data:
                p = photo(temp['pid'], temp['picurl'], temp['pcont'])
                list.append(p)
            aobj.photos = list
            return aobj
        except Exception as e:
            print('出错', e)
        finally:
            DBUtui.close(cursor, db)

    @staticmethod
    def findById(id):
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
            sql = "select id,title,tags,pubTime,readtimes,simpleinfo,Cont from articles where id={0}".format(id)
            cursor.execute(sql)
            data = cursor.fetchone()
            #封装元组数据
            aobj=Article(data['id'],data['title'],data['simpleinfo'],data['Cont'])
            aobj.tags=data['tags']
            aobj.pubTime=data['pubTime']
            aobj.readtimes=data['readtimes']
            return  aobj
        except Exception as e:
            print('出错', e)
        finally:
            DBUtui.close(cursor, db)

    @staticmethod
    def getDlist():
        # 查询日记列表
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor()
            sql = "select id,title,simpleinfo,MainPicUrl from articles where ShowType='文章' and Type='日记' and IsPub='是' ORDER BY PubTime desc"
            cursor.execute(sql)
            data = cursor.fetchall()  # 返回元组
            list = []
            for temp in data:
                # temp代表元组中的每一条信息
                # 将一组元组数据封装带对象UIData中
                p = UIData(temp[0], temp[1], temp[3], temp[2])
                list.append(p)
            return list
        except Exception as e:
            print('程序出错', e)
        finally:
            DBUtui.close(cursor, db)

    @staticmethod
    def tuiJian():
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
            sql = "select a.id aid,title from articles a,recarticles r where a.id=r.ArticleId and showtype='文章' and ispub='是' and PosId=1 order by weight desc;"
            cursor.execute(sql)
            adata = cursor.fetchall()# 返回元组

            if adata:
                return adata
        except Exception as e:
            print('程序出错', e)
        finally:
            DBUtui.close(cursor, db)

    @staticmethod
    def paiming():
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor()
            sql = "select id,title from articles where showtype='文章' and ispub='是' and type='日记'order by readtimes desc;"
            cursor.execute(sql)
            data = cursor.fetchall()# 返回元组
            adata = []
            for temp in data:
                adata.append(UIData(temp[0], temp[1], '',''))
            return adata
        except Exception as e:
            print('程序出错', e)
        finally:
            DBUtui.close(cursor, db)

    @staticmethod
    def Biaoqian():
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
            sql = "select distinct tags from articles where showtype='文章' and ispub='是' and type='日记';"
            cursor.execute(sql)
            bdata = cursor.fetchall()

            return bdata
        except Exception as e:
            print('程序出错', e)
        finally:
            DBUtui.close(cursor, db)

    @staticmethod
    def getTypelist(typeName):
        try:
            sql = "select id,title,simpleinfo,mainpicurl from articles where showtype='文章' and ispub='是' and type='{0}'".format(typeName)
            conn = DBUtui.getconnect()
            cur = conn.cursor()
            cur.execute(sql)

            data = cur.fetchall()
            list = []
            for d in data:
                list.append(UIData(d[0], d[1], d[3], d[2]))
            return list
        except Exception as e:
            print('出错', e)
        finally:
            DBUtui.close(cur, conn)

    @staticmethod  # 标签文章分类
    def getWZFL():
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor()
            sql = "select type,count(1),id from articles where SHOWType='文章'and ispub='是' group by type order by count(1) desc"
            cursor.execute(sql)
            data = cursor.fetchall()
            list = []
            for d in data:
                list.append(UIfl(d[0], d[1], d[2]))
            return list
        except Exception as e:
            print('程序出错', e)
        finally:
            DBUtui.close(cursor, db)
#首页搜索框
    @staticmethod
    def getsearch(value):
        try:
            sql = "select id,title,simpleInfo,MainPicUrl from articles where title like '%{0}%'".format(value)
            db = DBUtui.getconnect()
            cursor = db.cursor()
            cursor.execute(sql)
            data = cursor.fetchall()
            alist = []
            for d in data:
                alist.append(UIData(d[0], d[1], d[3], d[2]))
            return alist
        except Exception as e:
            print('出错', e)
        finally:
            DBUtui.close(cursor, db)
#日记搜索框
    @staticmethod
    def getsear(value):
        try:
            sql = "select id,title,simpleInfo,MainPicUrl from articles where  ShowType='文章' and ispub='是' and type='日记' and title like '%{0}%'".format(value)
            db = DBUtui.getconnect()
            cursor = db.cursor()
            cursor.execute(sql)
            data = cursor.fetchall()
            dlist = []
            for d in data:
                dlist.append(UIData(d[0], d[1], d[3], d[2]))
            return dlist
        except Exception as e:
            print('出错', e)
        finally:
            DBUtui.close(cursor, db)
#日记详情搜索框
    @staticmethod
    def gett(value):
        try:
            sql = "select id,title,simpleInfo,MainPicUrl from articles where  ShowType='文章' and ispub='是' and type='日记' and title like '%{0}%'".format(value)
            db = DBUtui.getconnect()
            cursor = db.cursor()
            cursor.execute(sql)
            data = cursor.fetchall()
            dlist = []
            for d in data:
                dlist.append(UIData(d[0], d[1], d[3], d[2]))
            return dlist
        except Exception as e:
            print('出错', e)
        finally:
            DBUtui.close(cursor, db)


class Userservice:
    @staticmethod
    def aboutme():
        # 关于我
        try:
            db = DBUtui.getconnect()
            cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
            sql = "select uid,selfslogo,selfphoto,aboutme from user;"
            cursor.execute(sql)
            data = cursor.fetchone()
            user = User(data['uid'], data['selfslogo'],data['selfphoto'],data['aboutme'])
            return user
        except Exception as e:
            print('程序出错', e)
        finally:
            DBUtui.close(cursor, db)
